Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and returns an array that describes the line. Because this function returns an array of values, it must be entered as an array formula.
The equation for the line is:
y = mx + b or
y = m1x1 + m2x2 + ... + b (if there are multiple ranges of x-values)
where the dependent y-value is a function of the independent x-values. The m-values are coefficients corresponding to each x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that LINEST returns is {mn,mn-1,...,m1,b}. LINEST can also return additional regression statistics.
Syntax
LINEST(known_y's,known_x's,const,stats)
Known_y's is the set of y-values you already know in the relationship y = mx + b.
If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable.
If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.
Known_x's is an optional set of x-values that you may already know in the relationship y = mx + b.
The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a vector (that is, a range with a height of one row or a width of one column).
If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.
Const is a logical value specifying whether to force the constant b to equal 0.
If const is TRUE or omitted, b is calculated normally.
If const is FALSE, b is set equal to 0 and the m-values are adjusted to fit y = mx.
Stats is a logical value specifying whether to return additional regression statistics.
If stats is TRUE, LINEST returns the additional regression statistics, so the returned array is {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.
If stats is FALSE or omitted, LINEST returns only the m-coefficients and the constant b.
The additional regression statistics are as follows.
Statistic | Description |
---|---|
se1,se2,...,sen | The standard error values for the coefficients m1,m2,...,mn. |
seb | The standard error value for the constant b (seb = #N/A when const is FALSE). |
r2 | The coefficient of determination. Compares estimated and actual y-values, and ranges in value from 0 to 1. If it is 1, there is a perfect correlation in the sample |
sey | The standard error for the y estimate. |
F | The F statistic, or the F-observed value. Use the F statistic to determine whether the observed relationship between the dependent and independent variables occurs by chance. |
df | The degrees of freedom. Use the degrees of freedom to help you find F-critical values in a statistical table. Compare the values you find in the table to the F statistic returned by LINEST to determine a confidence level for the model. For information about how df is calculated, see "Remarks" later in this topic. Example 4 below shows use of F and df. |
ssreg | The regression sum of squares. |
ssresid | The residual sum of squares. For information about how ssreg and ssresid are calculated, see "Remarks" later in this topic. |
The following illustration shows the order in which the additional regression statistics are returned.
Remarks
Slope (m):
To find the slope of a line, often written as m, take two points on the line, (x1,y1) and (x2,y2); the slope is equal to (y2 - y1)/(x2 - x1).
Y-intercept (b):
The y-intercept of a line, often written as b, is the value of y at the point where the line crosses the y-axis.
The equation of a straight line is y = mx + b. Once you know the values of m and b, you can calculate any point on the line by plugging the y- or x-value into that equation. You can also use the TREND function.
Slope:
=INDEX(LINEST(known_y's,known_x's),1)
Y-intercept:
=INDEX(LINEST(known_y's,known_x's),2)
where x and y are sample means, i.e., x = AVERAGE(known x's) and y = AVERAGE(known_y's).
Example 1 Slope and Y-Intercept
The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
|
|
Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A7:B7 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.
When entered as an array, the slope (2) and the y-intercept (1) are returned.
Example 2 Simple Linear Regression
The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
|
|
In general, SUM({m,b}*{x,1}) equals mx + b, the estimated y-value for a given x-value. You can also use the TREND function.
Example 3 Multiple Linear Regression
Suppose a commercial developer is considering purchasing a group of small office buildings in an established business district.
The developer can use multiple linear regression analysis to estimate the value of an office building in a given area based on the following variables.
Variable | Refers to the |
---|---|
y | Assessed value of the office building |
x1 | Floor space in square feet |
x2 | Number of offices |
x3 | Number of entrances |
x4 | Age of the office building in years |
This example assumes that a straight-line relationship exists between each independent variable (x1, x2, x3, and x4) and the dependent variable (y), the value of office buildings in the area.
The developer randomly chooses a sample of 11 office buildings from a possible 1,500 office buildings and obtains the following data. "Half an entrance" means an entrance for deliveries only.
The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
|
|
Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A14:E18 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is -234.2371645.
When entered as an array, the following regression statistics are returned. Use this key to identify the statistic you want.
The multiple regression equation, y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b, can now be obtained using the values from row 14:
y = 27.64*x1 + 12,530*x2 + 2,553*x3 - 234.24*x4 + 52,318
The developer can now estimate the assessed value of an office building in the same area that has 2,500 square feet, three offices, and two entrances and is 25 years old, by using the following equation:
y = 27.64*2500 + 12530*3 + 2553*2 - 234.24*25 + 52318 = $158,261
Or you can copy the following table to cell A21 of the example workbook.
Floor space (x1) | Offices (x2) | Entrances (x3) | Age (x4) | Assessed value (y) |
---|---|---|---|---|
2500 | 3 | 2 | 25 | =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14 |
You can also use the TREND function to calculate this value.
Example 4 Using the F and r2 Statistics
In the previous example, the coefficient of determination, or r2, is 0.99675 (see cell A17 in the output for LINEST), which would indicate a strong relationship between the independent variables and the sale price. You can use the F statistic to determine whether these results, with such a high r2 value, occurred by chance.
Assume for the moment that in fact there is no relationship among the variables, but that you have drawn a rare sample of 11 office buildings that causes the statistical analysis to demonstrate a strong relationship. The term "Alpha" is used for the probability of erroneously concluding that there is a relationship.
F and df in LINEST output can be used to assess the likelihood of a higher F value occurring by chance. F can be compared with critical values in published F-distribution tables or Excel’s FDIST can be used to calculate the probability of a larger F value occurring by chance. The appropriate F distribution has v1 and v2 degrees of freedom. If n is the number of data points and const = TRUE or omitted, then v1 = n – df – 1 and v2 = df. (If const = FALSE, then v1 = n – df and v2 = df.) Excel’s FDIST(F,v1,v2) will return the probability of a higher F value occurring by chance. In Example 4, df = 6 (cell B18) and F = 459.753674 (cell A18).
Assuming an Alpha value of 0.05, v1 = 11 – 6 – 1 = 4 and v2 = 6, the critical level of F is 4.53. Since F = 459.753674 is much higher than 4.53, it is extremely unlikely that an F value this high occurred by chance. (With Alpha = 0.05, the hypothesis that there is no relationship between known_y’s and known_x’s is to be rejected when F exceeds the critical level, 4.53.) Using Excel’s FDIST you can obtain the probability that an F value this high occurred by chance. FDIST(459.753674, 4, 6) = 1.37E-7, an extremely small probability. You can conclude, either by finding the critical level of F in a table or by using Excel’s FDIST, that the regression equation is useful in predicting the assessed value of office buildings in this area. Remember that it is critical to use correct values of v1 and v2 computed in the previous paragraph.
Example 5 Calculating the t-Statistics
Another hypothesis test will determine whether each slope coefficient is useful in estimating the assessed value of an office building in example 3. For example, to test the age coefficient for statistical significance, divide -234.24 (age slope coefficient) by 13.268 (the estimated standard error of age coefficients in cell A15). The following is the t-observed value:
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
If the absolute value of t is sufficiently high, it can be concluded that the slope coefficient is useful in estimating the assessed value of an office building in Example 3. The table below shows the absolute values of the 4 t-observed values.
If you consult a table in a statistics manual, you will find that t-critical, two tailed, with 6 degrees of freedom and Alpha = 0.05 is 2.447. This critical value can also be found using Excel’s TINV function. TINV(0.05,6) = 2.447. Because the absolute value of t, 17.7, is greater than 2.447, age is an important variable when estimating the assessed value of an office building. Each of the other independent variables can be tested for statistical significance in a similar manner. The following are the t-observed values for each of the independent variables.
Variable | t-observed value |
---|---|
Floor space | 5.1 |
Number of offices | 31.3 |
Number of entrances | 4.8 |
Age | 17.7 |
These values all have an absolute value greater than 2.447; therefore, all the variables used in the regression equation are useful in predicting the assessed value of office buildings in this area.<SPAN FPRev